﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Configuration;

namespace ProcessoSeletivo.DAO
{
    public class Usuario : IDisposable
    {
        #region " Listar " 

        public DataView ListarUsuario()
        {
            DataSet lRetorno = new DataSet();
            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("ListarUsuario", sql);
                sqlc.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter sqa = new SqlDataAdapter(sqlc);
                sqa.Fill(lRetorno, "tb_pse_usuario");
                sql.Close();
            }
            return lRetorno.Tables[0].DefaultView;
        }

        #endregion

        #region " Consultar "

        public DataView ConsultarUsuario(string des_login_usu)
        {
            DataSet lRetorno = new DataSet();
            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("ConsultarUsuario", sql);
                sqlc.CommandType = CommandType.StoredProcedure;

                sqlc.Parameters.AddWithValue("@des_login_usu", des_login_usu);

                SqlDataAdapter sqa = new SqlDataAdapter(sqlc);
                sqa.Fill(lRetorno, "tb_pse_usuario");
                sql.Close();
            }
            return lRetorno.Tables[0].DefaultView;
        }

        #endregion

        #region " Incluir "

        public void IncluirUsuario(DataSet dsRegistro)
        {
            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("IncluirUsuario", sql);
                sqlc.CommandType = CommandType.StoredProcedure;

                sqlc.Parameters.AddWithValue("@des_login_usu", dsRegistro.Tables[0].Rows[0]["des_login_usu"]);
                sqlc.Parameters.AddWithValue("@des_senha_usu", dsRegistro.Tables[0].Rows[0]["des_senha_usu"]);
                sqlc.Parameters.AddWithValue("@nom_usuario_usu", dsRegistro.Tables[0].Rows[0]["nom_usuario_usu"]);
                sqlc.Parameters.AddWithValue("@cod_endereco_end", dsRegistro.Tables[0].Rows[0]["cod_endereco_end"]);

                sqlc.ExecuteNonQuery();
                sql.Close();
            }
        }

        #endregion

        #region " Alterar "

        public void AlterarUsuario(DataSet dsRegistro)
        {
            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("AlterarUsuario", sql);
                sqlc.CommandType = CommandType.StoredProcedure;

                sqlc.Parameters.AddWithValue("@des_login_usu", dsRegistro.Tables[0].Rows[0]["des_login_usu"]);
                sqlc.Parameters.AddWithValue("@des_senha_usu", dsRegistro.Tables[0].Rows[0]["des_senha_usu"]);
                sqlc.Parameters.AddWithValue("@nom_usuario_usu", dsRegistro.Tables[0].Rows[0]["nom_usuario_usu"]);
                sqlc.Parameters.AddWithValue("@cod_endereco_end", dsRegistro.Tables[0].Rows[0]["cod_endereco_end"]);
                sqlc.ExecuteNonQuery();

                sql.Close();
            }
        }

        #endregion

        #region " Excluir "

        public void ExcluirUsuario(string des_login_usu)
        {
            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("ExcluirUsuario", sql);
                sqlc.CommandType = CommandType.StoredProcedure;

                sqlc.Parameters.AddWithValue("@des_login_usu", des_login_usu);
                sqlc.ExecuteNonQuery();

                sql.Close();
            }
        }

        #endregion

        #region " Validar "

        public bool ValidarUsuario(string des_login_usu, string des_senha_usu)
        {
            DataSet lRetorno = new DataSet();
            
            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("ValidarUsuario", sql);
                sqlc.CommandType = CommandType.StoredProcedure;

                sqlc.Parameters.AddWithValue("@des_login_usu", des_login_usu);
                sqlc.Parameters.AddWithValue("@des_senha_usu", des_senha_usu);

                SqlDataAdapter sqa = new SqlDataAdapter(sqlc);
                sqa.Fill(lRetorno, "tb_pse_usuario");
                sql.Close();
            }

            return lRetorno.Tables[0].Rows.Count > 0;
        }

        #endregion

        #region " Dipose "

        public void Dispose()
        {
            GC.SuppressFinalize(this);
        }

        #endregion
    }
}
